import java.io.*;
import javax.servlet.http.*;
import javax.servlet.*;
import java.sql.*;
import oracle.jdbc.*;
import oracle.jdbc.pool.*;

public class Player extends HttpServlet
{

    public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException
    {

        response.setContentType("text/html");
        PrintWriter out = response.getWriter();

        String dbUser = "jh3240"; // enter your username here
        String dbPassword = "kuzifQbw"; // enter your password here

        try
        {
            OracleDataSource ods = new oracle.jdbc.pool.OracleDataSource();
            ods.setURL("jdbc:oracle:thin:@//w4111g.cs.columbia.edu:1521/ADB");
            ods.setUser(dbUser);
            ods.setPassword(dbPassword);

            Connection conn = ods.getConnection();

            String query = new String();
            String query2 = new String();

            Statement s = conn.createStatement();

            String pid = request.getParameter("pid");

            query = "select p.name as pname, p.playerid, q.era as era,  t.name as tname, f.since as since, t.tid as tid "
                    + "from players p, pitchers q, plays_for f, teams t "
                    + "where p.playerid = f.playerid and t.tid = f.tid and p.playerid = q.playerid and p.playerid = '"
                    + pid + "'";

            query2 = "select p.name as pname, p.playerid, b.avg as avg, t.name as tname, f.since as since, t.tid as tid "
                    + "from players p, batters b, plays_for f, teams t "
                    + "where p.playerid = f.playerid and t.tid = f.tid and p.playerid = b.playerid and p.playerid = '"
                    + pid + "'";

            ResultSet r = s.executeQuery(query);
            int i = 0;
            double era = 0;
            boolean eraNull = false, pitched = false;
            while (r.next())
            {
                pitched = true;
                Date since = r.getDate("since");
                int year = since.getYear() + 1900;
                out.print(r.getString("pname") + " pitched for <a href=\"/team?teamid=" + r.getString("tid") + "&year=" + year + "\">" + r.getString("tname") + " in " + year + "</a>.<br />");
                era = r.getDouble("era");
                eraNull = r.wasNull();
            }
            if (pitched)
            {
                if (eraNull)
                    out.print("His ERA is NULL.<br />");
                else
                    out.print("His ERA is " + era + "<br />");
            }

            r.close();

            ResultSet r2 = s.executeQuery(query2);
            int j = 0;
            double avg = 0;
            boolean avgNull = false, batted = false;
            while (r2.next())
            {
                batted = true;
                Date since = r2.getDate("since");
                int year = since.getYear() + 1900;
                out.print(r2.getString("pname") + " batted for <a href=\"/team?teamid=" + r2.getString("tid") + "&year=" + year + "\">" + r2.getString("tname") + " in " + year + "</a>.<br />");
                avg = r2.getDouble("avg");
                avgNull = r2.wasNull();
            }
            if (batted)
            {
                if (avgNull)
                    out.print("His batting average is NULL.<br />");
                else
                    out.print("His batting average is " + avg + "<br />");
            }

            r2.close();
            s.close();

            conn.close();

        }
        catch (Exception e)
        {
            out.println("The database could not be accessed.<br>");
            out.println("More information is available as follows:<br>");
            e.printStackTrace(out);
        }

    } // end doGet method

} // end DatabaseServlet class

